﻿using Hsp.Auto.Interface;
using Hsp.Auto.Model.Models;
using System;
using System.Collections.Generic;
using System.Text;

namespace Hsp.Auto.MySql
{
    public class TableService : MySqlHelper, ITableService
    {
        //public TableService()
        //{
        //    //base.ConnectionString = MySqlHelperInit.ConnString;
        //}

        #region 获取当前数据库名称

        /// <summary>
        /// 获取当前数据库名称
        /// </summary>
        /// <returns></returns>
        public string CurrentDbName()
        {
            var result = ExecuteScalar("SELECT DATABASE();");
            return result == null ? "" : result.ToString();
        }

        #endregion

        #region 表列表信息

        /// <summary>
        /// 表列表信息
        /// </summary>
        /// <typeparam name="T">泛型类型</typeparam>
        /// <returns></returns>
        public List<T> TableList<T>()
        {
            return TableList<T>("", "");
        }

        /// <summary>
        /// 表列表信息
        /// </summary>
        /// <typeparam name="T">泛型类型</typeparam>
        /// <param name="dbName">库名称</param>
        /// <param name="tableNames">库表名称集合，逗号隔开</param>
        /// <returns></returns>
        public List<T> TableList<T>(string dbName, string tableNames)
        {
            var strWhere = string.Empty;
            if (!string.IsNullOrEmpty(dbName))
            {
                strWhere = string.Format(@" AND (t.`TABLE_SCHEMA` = '{0}')", dbName);
            }
            if (!string.IsNullOrEmpty(tableNames))
            {
                tableNames = tableNames.Trim().Replace(",", "','");
                strWhere = string.Format(@" AND (t.`TABLE_NAME` IN ('{0}'))", tableNames);
            }

            // 过滤筛选掉没有主键的表
            var strSql = string.Format(@"
            SELECT DISTINCT `Id`, `Name`, `Comment` FROM (
			    SELECT 0 AS `Id`, t.`TABLE_NAME` AS `Name`, t.`TABLE_COMMENT` AS `Comment` 
                FROM information_schema.TABLES t
                LEFT JOIN information_schema.KEY_COLUMN_USAGE fk ON fk.`TABLE_SCHEMA` = t.`TABLE_SCHEMA` AND fk.`TABLE_NAME` = t.`TABLE_NAME`
			    WHERE (1=1) AND (t.`TABLE_TYPE` = 'BASE TABLE')
                AND (fk.`COLUMN_NAME` IS NOT NULL)
                AND ((t.`TABLE_NAME` REGEXP '__') = 0)
                {0} 
            ) AS T 
            ORDER BY `Name`;
			", strWhere);
            return ListQuery<T>(strSql);
        }

        #endregion

        #region 表字段列表信息

        /// <summary>
        /// 表字段列表信息
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public List<T> ColumnList<T>()
        {
            return ColumnList<T>("", "");
        }

        /// <summary>
        /// 表字段列表信息
        /// </summary>
        /// <typeparam name="T">泛型类型</typeparam>
        /// <param name="dbName">库名称</param>
        /// <param name="tableNames">库表名称集合，逗号隔开</param>
        /// <returns></returns>
        public List<T> ColumnList<T>(string dbName, string tableNames)
        {
            var strWhere = string.Empty;
            if (!string.IsNullOrEmpty(dbName))
            {
                strWhere = string.Format(@" AND (c.table_schema = '{0}')", dbName);
            }
            if (!string.IsNullOrEmpty(tableNames))
            {
                tableNames = tableNames.Trim().Replace(",", "','");
                strWhere = string.Format(@" AND (c.table_name IN ('{0}'))", tableNames);
            }

            var strSql = string.Format(@"
			SELECT c.Ordinal_Position AS Id, c.Column_Name AS NAME, c.Data_Type AS DataType
            , CASE WHEN c.Is_Nullable = 'NO' THEN 0 ELSE 1 END AS Nullable
            , IFNULL(CASE WHEN IFNULL(c.Numeric_Precision, 0) > 0 THEN c.Numeric_Precision WHEN c.Character_Maximum_Length > 2147483647 THEN 2147483646 ELSE c.Character_Maximum_Length END, 0) AS LENGTH
            , IFNULL(c.Numeric_Scale, 0) AS Scale, CASE WHEN c.Column_Key = 'PRI' THEN 1 ELSE 0 END AS IsPK
            , CASE WHEN LENGTH(c.Extra) > 0 THEN 1 ELSE 0 END AS IsIdentity, c.Column_Comment AS COMMENT
            , c.COLUMN_DEFAULT AS DefaultValue
            , IFNULL(fk.REFERENCED_TABLE_NAME, '') AS Referenced_Table_Name, IFNULL(fk.REFERENCED_COLUMN_NAME, '') AS Referenced_Column_Name
            , c.Table_Name AS TableName, c.Column_Key AS ColumnKey
            , CAST(c.Column_Type AS CHAR(10000) CHARACTER SET utf8) AS ColumnType, IFNULL(c.Numeric_Precision, 0) AS Numeric_Precision
	        , CASE WHEN INSTR(c.Data_Type, 'blob') > 0 OR INSTR(c.Data_Type, 'binary') > 0 THEN 0 
	         WHEN INSTR(c.Data_Type, 'enum') > 0 THEN 0 WHEN INSTR(c.Data_Type, 'set') > 0 THEN 0
	         ELSE 1 END AS Queryable
			FROM information_schema.columns c
            INNER JOIN information_schema.TABLES t ON t.`TABLE_NAME` = c.`TABLE_NAME` AND t.`TABLE_SCHEMA` = c.`TABLE_SCHEMA`
			LEFT OUTER JOIN information_schema.KEY_COLUMN_USAGE fk ON fk.table_schema = c.table_schema AND fk.Table_Name = c.Table_Name AND fk.Column_Name = c.Column_Name
			/*LEFT OUTER JOIN information_schema.KEY_COLUMN_USAGE pk ON pk.REFERENCED_TABLE_SCHEMA = c.table_schema AND pk.REFERENCED_TABLE_NAME = c.Table_Name AND pk.REFERENCED_COLUMN_NAME = c.Column_Name*/
			WHERE (1=1) AND (t.TABLE_TYPE = 'BASE TABLE') {0} 
			ORDER BY c.Table_Name, c.Ordinal_Position;
			", strWhere); // AND (c.Data_Type NOT IN ('timestamp'))

            return ListQuery<T>(strSql);
        }

        #endregion

        #region 表主键列应用信息

        /// <summary>
        /// 表主键列应用信息
        /// </summary>
        /// <typeparam name="T">泛型类型</typeparam>
        /// <returns></returns>
        public List<T> KeyColumnList<T>()
        {
            return KeyColumnList<T>("");
        }

        /// <summary>
        /// 表主键列应用信息
        /// </summary>
        /// <typeparam name="T">泛型类型</typeparam>
        /// <param name="dbName">数据库名称</param>
        /// <returns></returns>
        public List<T> KeyColumnList<T>(string tableNames)
        {
            return KeyColumnList<T>("", tableNames);
        }

        /// <summary>
        /// 表主键列应用信息
        /// </summary>
        /// <typeparam name="T">泛型类型</typeparam>
        /// <param name="dbName">数据库名称</param>
        /// <param name="tableNames">表名称集合</param>
        /// <returns></returns>
        public List<T> KeyColumnList<T>(string dbName, string tableNames)
        {

            var strWhere = string.Empty;
            if (!string.IsNullOrEmpty(dbName))
            {
                strWhere = string.Format(@" AND (c.table_schema = '{0}')", dbName);
            }
            if (!string.IsNullOrEmpty(tableNames))
            {
                tableNames = tableNames.Trim().Replace(",", "','");
                strWhere = string.Format(@" AND (c.table_name IN ('{0}'))", tableNames);
            }

            var strSql = string.Format(@"
            SELECT fk.`TABLE_NAME` AS `TableName`, fk.`COLUMN_NAME` AS `ColumnName`
            , fk.`CONSTRAINT_NAME` AS `ConstraintName`, fk.`REFERENCED_TABLE_NAME` AS `ReferencedTableName`
            , fk.`REFERENCED_COLUMN_NAME` AS `ReferencedColumnName`
            FROM information_schema.columns c
            INNER JOIN information_schema.KEY_COLUMN_USAGE fk ON fk.`TABLE_SCHEMA` = c.`TABLE_SCHEMA` AND fk.`TABLE_NAME` = c.`TABLE_NAME` AND fk.`COLUMN_NAME` = c.`COLUMN_NAME`
            WHERE (1=1) AND (`REFERENCED_TABLE_NAME` IS NOT NULL) {0}
            ORDER BY c.`TABLE_NAME`;
			", strWhere);

            return ListQuery<T>(strSql);

        }

        #endregion


        #region 表被引用数据信息

        public List<T> ReferenceList<T>()
        {
            return ReferenceList<T>("");
        }

        public List<T> ReferenceList<T>(string tableNames)
        {
            return ReferenceList<T>("", tableNames);
        }

        /// <summary>
        /// 表被引用数据信息
        /// </summary>
        /// <typeparam name="T">泛型类型</typeparam>
        /// <returns></returns>
        public List<T> ReferenceList<T>(string dbName, string tableNames)
        {

            var strWhere = string.Empty;
            if (!string.IsNullOrEmpty(dbName))
            {
                strWhere = string.Format(@" AND (c.table_schema = '{0}')", dbName);
            }
            if (!string.IsNullOrEmpty(tableNames))
            {
                tableNames = tableNames.Trim().Replace(",", "','");
                strWhere = string.Format(@" AND (c.table_name IN ('{0}'))", tableNames);
            }

            var strSql = string.Format(@"
			SELECT c.Ordinal_Position AS Id, c.Column_Name AS NAME, c.Table_Name AS TableName
            , IFNULL(pk.TABLE_NAME, '') AS PrimarykeyTableName
            , IFNULL(pk.COLUMN_NAME, '') AS PrimarykeyColumnName
            , IFNULL(pk.CONSTRAINT_NAME, '') AS ConstraintName
			FROM information_schema.columns c
			INNER JOIN information_schema.KEY_COLUMN_USAGE pk ON pk.REFERENCED_TABLE_SCHEMA = c.table_schema AND pk.REFERENCED_TABLE_NAME = c.Table_Name AND pk.REFERENCED_COLUMN_NAME = c.Column_Name
			WHERE (1=1) {0} 
			ORDER BY c.Table_Name, c.Ordinal_Position;
			", strWhere);

            return ListQuery<T>(strSql);

        }

        #endregion

        #region 表注释中可添加枚举数据信息

        /// <summary>
        /// 表注释中可添加枚举数据信息
        /// </summary>
        /// <typeparam name="T">泛型类型</typeparam>
        /// <returns></returns>
        public List<T> EnumList<T>(string dbName)
        {
            var strSql = string.Format(@"
            SELECT Column_Name AS NAME
            , Column_Comment AS Comment
            FROM information_schema.columns
            WHERE table_schema='{0}' 
            AND LENGTH(Column_Comment) > 0
            AND (INSTR(Column_Comment, '：') > 0)
            AND (Data_Type = 'int')
            GROUP BY Column_Name, Column_Comment
            ORDER BY Column_Comment;
			", dbName);

            return ListQuery<T>(strSql);
        }

        public List<T> ColumnList<T>(string tableNames)
        {
            return ColumnList<T>("", tableNames);
        }


        public List<T> EnumList<T>()
        {
            return EnumList<T>("");
        }

        #endregion

        #region 表索引数据信息

        /// <summary>
        /// 表索引数据信息
        /// </summary>
        /// <typeparam name="T">泛型类型</typeparam>
        /// <returns></returns>
        public List<T> IndexList<T>()
        {
            return IndexList<T>("");
        }

        /// <summary>
        /// 表索引数据信息
        /// </summary>
        /// <typeparam name="T">泛型类型</typeparam>
        /// <param name="tableNames">表名称集合</param>
        /// <returns></returns>
        public List<T> IndexList<T>(string tableNames)
        {
            return IndexList<T>("", tableNames);
        }

        /// <summary>
        /// 表索引数据信息
        /// </summary>
        /// <typeparam name="T">泛型类型</typeparam>
        /// <param name="dbName">数据库名称</param>
        /// <param name="tableNames">表名称集合</param>
        /// <returns></returns>
        public List<T> IndexList<T>(string dbName, string tableNames)
        {
            var strWhere = string.Empty;
            if (!string.IsNullOrEmpty(dbName))
            {
                strWhere = string.Format(@" AND (`TABLE_SCHEMA` = '{0}')", dbName);
            }
            if (!string.IsNullOrEmpty(tableNames))
            {
                tableNames = tableNames.Trim().Replace(",", "','");
                strWhere = string.Format(@" AND (`TABLE_NAME` IN ('{0}'))", tableNames);
            }

            var strSql = string.Format(@"
                SELECT `TABLE_NAME` AS `TableName`, `INDEX_NAME` AS `IndexName`
                , `NON_UNIQUE` AS `NonUnique`
                , GROUP_CONCAT(`COLUMN_NAME`) AS `IndexColumns`
                FROM information_schema.statistics
                WHERE (1=1){0}
                GROUP BY `TABLE_NAME`, `NON_UNIQUE`, `INDEX_NAME`;
			", strWhere);

            return ListQuery<T>(strSql);
        }



        #endregion

        public List<Columns> SqliteColumnList<T>(string tableNames)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// 视图列表信息
        /// </summary>
        /// <typeparam name="T">泛型类型</typeparam>
        /// <param name="dbName">库名称</param>
        /// <returns></returns>
        public List<T> ViewList<T>(string dbName)
        {
            var strWhere = string.Empty;
            if (!string.IsNullOrEmpty(dbName))
            {
                strWhere = string.Format(@" AND (v.`TABLE_SCHEMA` = '{0}')", dbName);
            }

            var strSql = string.Format(@"
                SELECT v.`TABLE_NAME` AS `Name`
                FROM `information_schema`.`VIEWS` v
                WHERE (1=1){0}
                ORDER BY v.`TABLE_NAME`;", strWhere);

            return ListQuery<T>(strSql);
        }

        /// <summary>
        /// 视图字段列表信息
        /// </summary>
        /// <typeparam name="T">泛型类型</typeparam>
        /// <param name="dbName">库名称</param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public List<T> ViewColumnList<T>(string dbName)
        {
            var strWhere = string.Empty;
            if (!string.IsNullOrEmpty(dbName))
            {
                strWhere = string.Format(@" AND (v.`TABLE_SCHEMA` = '{0}')", dbName);
            }

            var strSql = string.Format(@"
                SELECT c.`ORDINAL_POSITION` AS `Id`, v.`TABLE_NAME` AS `TableName`, c.`COLUMN_NAME` AS `Name`, c.`DATA_TYPE` AS `DataType`
                , CASE WHEN c.`IS_NULLABLE` = 'NO' THEN 0 ELSE 1 END AS `Nullable`
                , IFNULL(CASE WHEN IFNULL(c.Numeric_Precision, 0) > 0 THEN c.Numeric_Precision WHEN c.Character_Maximum_Length > 2147483647 THEN 2147483646 ELSE c.Character_Maximum_Length END, 0) AS LENGTH
                , IFNULL(c.`NUMERIC_SCALE`, 0) AS `Scale`, c.`COLUMN_DEFAULT` AS `DefaultValue`
                , CAST(c.`COLUMN_TYPE` AS CHAR(10000) CHARACTER SET utf8) AS `ColumnType`, IFNULL(c.`NUMERIC_PRECISION`, 0) AS `Numeric_Precision`
                , c.`COLUMN_COMMENT` AS `Comment`
                FROM `information_schema`.`VIEWS` v
                INNER JOIN `information_schema`.`COLUMNS` c ON c.`TABLE_SCHEMA` = v.`TABLE_SCHEMA` AND c.`TABLE_NAME` = v.`TABLE_NAME`
                WHERE (1=1) {0}
                ORDER BY v.`TABLE_NAME`, c.`ORDINAL_POSITION`;", strWhere);

            return ListQuery<T>(strSql);
        }
    }
}
